﻿//Copyright (C) <2013>  <jonathan cleeve norton> All Rights Reserved 
//Contact jon.norton@fin-plus.co.uk website <http://www.fin-plus.co.uk/>
using System;
using System.Text;
using System.Data;
using System.Data.SqlServerCe;
using FinPlusAssembler;
using FinPlusInterfaces;

namespace FinPlusConnDB
{
    public sealed class FinPlusQuery : IDisposable, IQuery
    {
        public IPods Values  { get; private set; }
        public ConType ConType { get; private set; }
        public string QueryString { get; private set; }
        public string Group { get; private set; }

        private ITransport _tran;
        private const string _dbDateFormat = "yyyy-MM-dd HH:mm:ss";
        private readonly SqlCeConnection _conn;
        private readonly QueryType _queryType;
        private readonly DateTime _asOf, _from;
        private readonly string _table, _tableId, _where, _user;
       
        public FinPlusQuery(SqlCeConnection conn, ITransport tran, QueryType queryType, string group, DateTime asOf, DateTime from, string where, ConType conType)
        {
            Values = IOC.New<IPods>();
            _queryType = queryType;
            _conn = conn;
            _tran = tran;
            _table = group + "Beans";
            _tableId = group + "Ids";
            _asOf = asOf;
            _from = from;
            _where = where;
            Group = group;
            ConType = conType;
            QueryString = QString(_queryType);
        }

        public FinPlusQuery(IPod value, SqlCeConnection conn, ITransport tran, QueryType queryType, string group, string user)
        {
            Values = IOC.New<IPods>();
            Values.Add(value, false, false);
            _conn = conn;
            _tran = tran;
            _queryType = queryType;
            Group = group;
            _table = group + "Beans";
            _tableId = group + "Ids";
            _user = user;

            QueryString = QString(_queryType);
        }

        public bool Update(IPod value)
        {
            if (!value.IsMatch(_where))
            {
                if (Values.ContainsKey(value.Id)) 
                    Values.Update(value, ChangeType.Removed);
                return true;
            }

            if(_queryType == QueryType.Select || _queryType == QueryType.SelectFrom)
            {
                Values.Update(value, value.IsClosed ?  ChangeType.Removed : (Values.ContainsKey(value.Id) ? ChangeType.Altered : ChangeType.New));
                return true; 
            }

            return false; 
        }

        public void Execute()
        {
            switch (_queryType)//executes go through one piont
            {
                case QueryType.SelectAsOf:  
                    ExecuteSelect(QueryString); break;
                case QueryType.Select:
                case QueryType.SelectFrom:  
                    if (ConType != ConType.None) 
                        _tran.Listen(Group, false); 
                        ExecuteSelect(QueryString); break;
                case QueryType.SavePod: ExecuteSave(QueryString); break;
                case QueryType.ClosePod: ExecuteClose(QueryString); break;
            }
        }

        public void Dispose()
        {
            _tran.Listener.Stop(Group);
        }

        //private
        private void ExecuteSelect(string query)
        {
            try
            {
                var tempBean = IOC.New<IBean>();
                var tempValue = IOC.New<IPod>();

                using (var cmd = new SqlCeCommand(query, _conn))
                {
                    using (var r = cmd.ExecuteReader())
                    {
                        while (r.Read())
                        {
                            var index = int.Parse(r["Index"].ToString());
                            var id = int.Parse( r["Id"].ToString());
                            var timeStamp = DateTime.Parse(r["TimeStamp"].ToString());
                            var type = IOC.FindType(r["Type"].ToString());
                            var bean = tempBean.Clone(index, id, r["Name"].ToString(), r["Value"].ToString(), type, timeStamp, r["User"].ToString());

                            IPod value;
                            if(Values.ContainsKey(bean.Id))
                                value = Values[bean.Id];
                            else
                                Values.Add(value = tempValue.Create(bean.Id), false, false);

                            if (bean.Name.Equals("IsClosed")) 
                                value.IsClosed =  (bool)bean.Object;
                            
                            value.TryAdd(bean);
                        }
                    }
                }

                if(!string.IsNullOrEmpty(_where))
                    Values.Remove(_where.Replace("'", "\"")); //where can macth on hitorical data some cases might need some might not put this in for now
            }
            catch(Exception e)
            {
                throw new QueryExecuteException(QueryString, e);
            }
        }

        private void ExecuteSave(string query, bool withPublish = true)
        {
            var timeStamp = DateTime.Now;

            foreach (var pod in Values.Values)
            {
                try 
                {
                    if (pod.Id < 1) 
                        GetPodId(QString(QueryType.GetPodId), pod);
                    
                    var publish = false;

                    using (var cmdSave = new SqlCeCommand(query, _conn))
                    {
                        cmdSave.Parameters.Add("@Id", SqlDbType.Int);
                        cmdSave.Parameters.Add("@Index", SqlDbType.Int);
                        cmdSave.Parameters.Add("@Name", SqlDbType.NVarChar, 50);
                        cmdSave.Parameters.Add("@Value", SqlDbType.NVarChar, 200);
                        cmdSave.Parameters.Add("@Type", SqlDbType.NVarChar, 50);
                        cmdSave.Parameters.Add("@TimeStamp", SqlDbType.DateTime);
                        cmdSave.Parameters.Add("@User", SqlDbType.NVarChar, 50);

                        foreach (var bean in pod)
                        {
                            if (bean.TimeStamp == DateTime.MinValue || bean.Id < 0)
                            {
                                bean.Id = pod.Id;
                                bean.TimeStamp = timeStamp;
                                publish = true;
                                
                                if (bean.Name.Equals("Id"))
                                    bean.Update(pod.Id, null, false); 

                                if (bean.Name.Equals("TimeStamp"))
                                    bean.Update(timeStamp, null, false);
                            
                                cmdSave.Parameters["@Id"].Value = bean.Id;
                                cmdSave.Parameters["@Index"].Value = bean.Index;
                                cmdSave.Parameters["@Name"].Value = bean.Name;
                                cmdSave.Parameters["@Value"].Value = bean.ToString();
                                cmdSave.Parameters["@Type"].Value = bean.TypeName;
                                cmdSave.Parameters["@TimeStamp"].Value = timeStamp;
                                cmdSave.Parameters["@User"].Value = bean.User ?? Environment.UserName;
                                cmdSave.ExecuteNonQuery();
                            }
                        }
                    }

                    ExecuteLastChanged(QString(QueryType.LastChanged), pod.Id, timeStamp);

                    if (publish && withPublish) 
                        _tran.Publish(IOC.New<IMessage>(pod.ToArray(), Group, ActionType.New));//for real-time, you could just publish individual dirty beans for better deserialisatin
                }
                catch (Exception e)
                {
                    throw new QueryExecuteException(QueryString, e);
                }
            }
        }

        private void ExecuteLastChanged(string query, int id, DateTime timeStamp)
        {
            using (var cmdChange = new SqlCeCommand(query, _conn))
            {
                cmdChange.Parameters.Add("@LastChanged", SqlDbType.DateTime);
                cmdChange.Parameters["@LastChanged"].Value = timeStamp;
                cmdChange.Parameters.Add("@Id", SqlDbType.Int);
                cmdChange.Parameters["@Id"].Value = id;
                cmdChange.ExecuteNonQuery();
            }
        }

        private void ExecuteClose(string query)
        {
            foreach (var pod in Values.Values)//close
            {
                try
                {
                    using (var cmdClose = new SqlCeCommand(query + pod.Id, _conn))
                        cmdClose.ExecuteNonQuery();

                    pod.Add("IsClosed", pod.IsClosed = true, _user);
                    ExecuteSave(QString(QueryType.SavePod),  false);

                    _tran.Publish(IOC.New<IMessage>(pod.ToArray(), Group, ActionType.Close));//for real-time, you could just publish individual dirty beans for better deserialization
                }
                catch (Exception e)
                {
                    throw new QueryExecuteException(QueryString, e);
                }
            }
        }

        private void GetPodId(string query, IPod value)
        {
            try
            {
                using (var cmd = new SqlCeCommand(query, _conn))
                {
                    cmd.Parameters.Add("@TimeStamp", SqlDbType.DateTime).Value = DateTime.Now;
                    cmd.Parameters.Add("@LastChanged", SqlDbType.DateTime).Value = DateTime.Now;
                    cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = _user;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "SELECT @@IDENTITY";
                    //note CE does not support SCOPE_IDENTITY
                    value.Id = int.Parse(cmd.ExecuteScalar().ToString());
                }
            }
            catch(Exception e)
            {
                throw new QueryExecuteException("Could not get pod id", e);
            }
        }

        private string QString(QueryType queryType)
        {
            //action query strings
            if (queryType == QueryType.LastChanged) 
                return string.Format("UPDATE [{0}] SET LastChanged = @LastChanged WHERE [Id] = @Id", _tableId);

            if (queryType == QueryType.GetPodId) 
                return string.Format(@"INSERT INTO [{0}] ([TimeStamp], [LastChanged], [User]) VALUES (@TimeStamp, @LastChanged, @Name)", _tableId);

            if (queryType == QueryType.SavePod) 
                return string.Format(@"INSERT INTO [{0}] ([Index], [Id] ,[Name], [Value], [Type], [TimeStamp], [User]) VALUES (@Index, @Id, @Name, @Value, @Type, @TimeStamp, @User)", _table);

            if (queryType == QueryType.ClosePod) 
                return string.Format(@"UPDATE [{0}] SET Closed = '{1}', LastChanged = '{1}', ClosedBy = '{2}'  WHERE [Id] = ", _tableId, DateTime.Now.ToString(_dbDateFormat), _user);

            //select query strings
            var s = new StringBuilder().Append(string.Format(@"SELECT * FROM {0} WHERE", _table));
            bool needsAnd = false;
            if (_where != string.Empty) //where clause on any bean in pod 
            {
                s.Append(string.Format(@" Id IN(SELECT Id FROM {0} WHERE {1})", _table, _where));
                needsAnd = true;
            }

            //AsOf
            if (_asOf != DateTime.MinValue && ConType == ConType.Amend) //created before a set date and not closed before set date
            {
                if (needsAnd) 
                    s.Append(@" AND");
                s.Append(string.Format(@" Id IN(SELECT Id FROM {0} WHERE TimeStamp < '{1}' AND  LastChanged > '{1}' AND (Closed > '{1}' OR Closed IS NULL)) AND TimeStamp < '{1}'", _tableId, _asOf.ToString(_dbDateFormat)));
                needsAnd = true;
            }
            else if (_asOf != DateTime.MinValue) //created before a set date and not closed before set date
            {
                if (needsAnd) 
                    s.Append(@" AND");
                s.Append(string.Format(@" Id IN(SELECT Id FROM {0} WHERE TimeStamp < '{1}' AND (Closed > '{1}' OR Closed IS NULL)) AND TimeStamp < '{1}'", _tableId, _asOf.ToString(_dbDateFormat)));
                needsAnd = true;
            }
            //From
            else if (_from != DateTime.MinValue && ConType == ConType.All) //created from a set date amends, new and closes.
            {
                if (needsAnd) 
                    s.Append(@" AND");
                s.Append(string.Format(@" Id IN(SELECT Id FROM {0} WHERE LastChanged > '{1}')", _tableId, _from.ToString(_dbDateFormat)));
            }
            else if (_from != DateTime.MinValue && ConType == ConType.NewAndAmend) //created from a set new and amends only.
            {
                if (needsAnd) 
                    s.Append(@" AND");
                s.Append(string.Format(@" Id IN(SELECT Id FROM {0} WHERE LastChanged > '{1}' AND Closed IS NULL )", _tableId, _from.ToString(_dbDateFormat)));
            }
            else if (_from != DateTime.MinValue && ConType == ConType.New) //created from a set new only.
            {
                if (needsAnd) 
                    s.Append(@" AND");
                s.Append(string.Format(@" Id IN(SELECT Id FROM {0} WHERE TimeStamp > '{1}' AND Closed IS NULL )", _tableId, _from.ToString(_dbDateFormat)));
            }
            else if (_from != DateTime.MinValue && ConType == ConType.Amend) //created from a set date amends only.
            {
                if (needsAnd) 
                    s.Append(@" AND");
                s.Append(string.Format(@" Id IN(SELECT Id FROM {0} WHERE TimeStamp < '{1}' AND LastChanged > '{1}' AND Closed IS NULL )", _tableId, _from.ToString(_dbDateFormat)));
            }
            //select
            else
            {
                if (needsAnd) 
                    s.Append(@" AND");
                s.Append(string.Format(@" Id IN(SELECT Id FROM {0} WHERE Closed IS NULL )", _tableId));
            }

            return s.ToString();
        }
   }
}
